package com.hd123.rumba.patch.net.sf.hibernate.connection;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.cfg.Environment;
import net.sf.hibernate.connection.ConnectionProvider;
import net.sf.hibernate.connection.ConnectionProviderFactory;
import net.sf.hibernate.util.PropertiesHelper;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.pool.KeyedObjectPoolFactory;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * modify statement pool settings
 * 
 * @author caili, 2006-10-25
 */
// 修正错误: oracle: too many open cursors
// 重复步骤:
// 1. 执行 报表查询 报表菜单 库存管理 库存.库存资金查询
// 2. 查看oracle中open cursor
// select count(1) , b.sid, b.USERNAME, b.MACHINE
// from v$open_cursor a, v$session b
// where a.SID = b.SID and username='XXX'
// group by b.sid, b.USERNAME, b.MACHINE
// order by b.sid
// 重复执行1-2, 发现open cursor不断增加.
// 这将导致open cursor超过300个(oracle默认设置值)后, oracle报告too many open cursors.
//   
// 原因:
// 1. 此查询使用了jdbc查询, 并且使用了conn.prepareStatement()
// 2. 查询源和查询结果都使用了dbcp连接池
// 3. dbcp连接池缓冲了Connection和PreparedStatement. (connectionPool, statementPool)
// 4. 由于PreparedStatement一直被缓存在statementPool中, 导致oracle的cursor不会被关闭,
// 同时也导致server内存占用不会被释放
// 5. 虽然common-dbcp-1.2.1会在某个条件下清空statementPool
// org.apache.commons.pool.impl.GenericKeyedObjectPool
// public synchronized Object borrowObject(Object key)
// if ((_maxTotal > 0) && (_totalActive + _totalIdle >= _maxTotal)) {
// clear();
// }
// 但是Hibernate2.1.2中在创建statementPool时, 没有指明maxTotal,
// 使得_maxTotal总是default值(-1), 而不会清除statementPool内容.
// net.sf.hibernate.connection.DBCPConnectionProvider
// public void configure(Properties props)
// statementPool = new GenericKeyedObjectPoolFactory(
// null,
// Integer.parseInt( props.getProperty(Environment.DBCP_PS_MAXACTIVE) ),
// Byte.parseByte( props.getProperty(Environment.DBCP_PS_WHENEXHAUSTED) ),
// Long.parseLong( props.getProperty(Environment.DBCP_PS_MAXWAIT) ),
// Integer.parseInt( props.getProperty(Environment.DBCP_PS_MAXIDLE) )
// );
//   
// 解决方法:
// 用这个类来替代net.sf.hibernate.connection.DBCPConnectionProvider
// 同时设置
// <property name="hibernate.dbcp.ps.maxTotal">数字</property>
// 这样, 当缓存的preparedStatement超过maxTotal指定的数字时, 整个statementPool就会被清空.
// 另外, 如果设置了
// <property name="hibernate.dbcp.ps.enabled">false</property>
// 那么就不会启用statement pool (默认值true)
//
public class DBCPConnectionProvider implements ConnectionProvider {
  private Integer isolation;
  private DataSource ds;
  private KeyedObjectPoolFactory statementPool;
  private ObjectPool connectionPool;

  private static final Log log = LogFactory.getLog(DBCPConnectionProvider.class);

  public Connection getConnection() throws SQLException {
    final Connection c = ds.getConnection();
    if (isolation != null)
      c.setTransactionIsolation(isolation.intValue());
    if (c.getAutoCommit())
      c.setAutoCommit(false);
    return c;
  }

  public void closeConnection(Connection conn) throws SQLException {
    conn.close();
  }

  public void configure(Properties props) throws HibernateException {
    String jdbcDriverClass = props.getProperty(Environment.DRIVER);
    String jdbcUrl = props.getProperty(Environment.URL);
    Properties connectionProps = ConnectionProviderFactory.getConnectionProperties(props);

    log.info("DBCP using driver: " + jdbcDriverClass + " at URL: " + jdbcUrl);
    log.info("Connection properties: " + connectionProps);

    if (jdbcDriverClass == null) {
      log.warn("No JDBC Driver class was specified by property " + Environment.DRIVER);
    } else {
      try {
        Class.forName(jdbcDriverClass);
      } catch (ClassNotFoundException cnfe) {
        String msg = "JDBC Driver class not found: " + jdbcDriverClass;
        log.fatal(msg);
        throw new HibernateException(msg);
      }
    }

    try {

      // We'll need a ObjectPool that serves as the
      // actual pool of connections.
      connectionPool = new GenericObjectPool(null, Integer.parseInt(props
          .getProperty(Environment.DBCP_MAXACTIVE)), Byte.parseByte(props
          .getProperty(Environment.DBCP_WHENEXHAUSTED)), Long.parseLong(props
          .getProperty(Environment.DBCP_MAXWAIT)), Integer.parseInt(props
          .getProperty(Environment.DBCP_MAXIDLE)), PropertiesHelper.getBoolean(
          Environment.DBCP_VALIDATION_ONBORROW, props), PropertiesHelper.getBoolean(
          Environment.DBCP_VALIDATION_ONRETURN, props));

      // ------- begin--------
      // caili 2006-10-25
      boolean enableStmtPool = Boolean.parseBoolean(props.getProperty("hibernate.dbcp.ps.enabled",
          "true"));
      if (enableStmtPool) {
        // We'll need a KeyedObjectPoolFactory that serves as the
        // actual pool of prepared statements.
        statementPool = new GenericKeyedObjectPoolFactory(null, Integer.parseInt(props
            .getProperty(Environment.DBCP_PS_MAXACTIVE)), Byte.parseByte(props
            .getProperty(Environment.DBCP_PS_WHENEXHAUSTED)), Long.parseLong(props
            .getProperty(Environment.DBCP_PS_MAXWAIT)), Integer.parseInt(props
            .getProperty(Environment.DBCP_PS_MAXIDLE)), Integer.parseInt(props.getProperty(
            "hibernate.dbcp.ps.maxTotal", "-1")));
      } else
        statementPool = null;
      // ------- end ---------

      // Next, we'll create a ConnectionFactory that the
      // pool will use to create Connections.
      // We'll use the DriverManagerConnectionFactory.
      ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(jdbcUrl,
          connectionProps);

      // Now we'll create the PoolableConnectionFactory, which wraps
      // the "real" Connections created by the ConnectionFactory with
      // the classes that implement the pooling functionality.
      String validationQuery = props.getProperty(Environment.DBCP_VALIDATION_QUERY);
      new PoolableConnectionFactory(connectionFactory, connectionPool, statementPool,
          validationQuery, false, false);

      // Finally, we create the PoolingDriver itself,
      // passing in the object pool we created.
      ds = new PoolingDataSource(connectionPool);

    } catch (Exception e) {
      log.fatal("could not instantiate DBCP connection pool", e);
      throw new HibernateException("Could not instantiate DBCP connection pool", e);
    }

    String i = props.getProperty(Environment.ISOLATION);
    if (i == null) {
      isolation = null;
    } else {
      isolation = new Integer(i);
      log.info("JDBC isolation level: " + Environment.isolationLevelToString(isolation.intValue()));
    }

  }

  /**
   * @see net.sf.hibernate.connection.ConnectionProvider#close()
   */
  public void close() throws HibernateException {
    try {
      connectionPool.close();
    } catch (Exception e) {
      throw new HibernateException("could not close DBCP pool", e);
    }
  }

}
